--------------------
-- 建库部分
--------------------
-- 创建学生管理系统数据库，名称为Student
use master;
go
-- 检测原库是否存在，如果存在则删除
if exists (select * from sys.databases where name = 'Student')
	drop database Student;
go

-- 创建一个库
create database Student;
go
-- 使用这个库
use Student;
go


--------------------
-- 建表部分
--------------------
-- 创建班级表，存储班级信息，其中字段包含：班级id、班级名称
create table Class(
	ClassId int not null identity(1,1),
	ClassName nvarchar(50) not null
);
go

-- 创建学生表，存储学生信息，其中字段保护：学生id、姓名、性别、生日、家庭住址，所属班级id
create table Student (
	StudentId int not null identity(1, 1),
	StudentName nvarchar(50),
	StudentSex tinyint not null,--0：保密，1：男，2：女
	StudentBirth date,
	StudentAddress nvarchar(255) not null
);
go

-- 创建课程表，存储课程信息，其中字段包含：课程id、课程名称、课程学分
create table Course(
	CourseId int identity(1,1),
	CourseName nvarchar(50),
	CourseCredit int
);
go

-- 创建班级课程表，存储班级课程信息，其中字段包含：自增id、班级id、课程id
create table ClassCourse(
	ClassCourseId int identity(1,1),
	ClassId int,
	CourseId int
);
go

-- 创建分数表，存储学生每个课程分数信息，其中字段包含：分数id、学生id、课程id、分数
create table Score(
	ScoreId int identity(1,1),
	StudentId int,
	CourseId int,
	Score int
);
go


-- 学生表建好了，细想一下少了一个所属班级的字段
-- 给学生表 Student 增加一个所属班级id字段 
alter table Student add ClassId int not null;
go

----------------------------------------
-- 创建约束部分，使用alter进行修改
----------------------------------------
-- 班级表 ClassId 字段需要设置为主键（主键约束）
alter table Class add constraint PK_Class_ClassId primary key (ClassId);
-- 学生表 StudentId 字段需要设置为主键（主键约束）
alter table Student add constraint PK_Student_StudentId primary key (StudentId);
-- 课程表 CourseId 字段需要设置为主键（主键约束）
alter table Course add constraint PK_Course_CourseId primary key (CourseId);
-- 班级课程表 ClassCourseId 字段需要设置为主键（主键约束）
alter table ClassCourse add constraint PK_ClassCourse_ClassCourseId primary key (ClassCourseId);
-- 分数表 ScoreId 字段需要设置为主键（主键约束）
alter table Score add constraint PK_Score_ScoreId primary key (ScoreId);

-- 学生表 StudentName 不允许为空（非空约束）
alter table Student alter column StudentName nvarchar(50) not null;

-- 班级表 ClassName 需要唯一（唯一约束）
alter table Class add constraint UQ_Class_ClassName unique(ClassName);
-- 课程表 CourseName 需要唯一（唯一约束）
alter table Course add constraint UQ_Course_CourseName unique(CourseName);

-- 学生表 ClassId 增加默认值为0（默认值约束）
alter table Student add constraint DF_Student_ClassId default(0) for ClassId;

-- 学生表 StudentSex 只能为1或者2（Check约束）
alter table Student add constraint CK_Student_StudentSex check(StudentSex=1 or StudentSex=2 or StudentSex=3);
-- 分数表 Score 字段只能大于等于0（check约束）
alter table Score add constraint CK_Score_Score check(Score>=0);
-- 课程表 CourseCredit 字段只能大于0（check约束）
alter table Course add constraint CK_Course_CourseCredit check(CourseCredit>=0);

-- 班级课程表ClassId 对应是 班级表ClassId 的外键 （外键约束）
alter table ClassCourse add constraint FK_ClassCourse_ClassId foreign key (ClassId) references Class(ClassId);
-- 班级课程表CourseId 对应是 课程表CourseId 的外键 （外键约束）
alter table ClassCourse add constraint FK_ClassCourse_CourseId foreign key (CourseId) references Course(CourseId);
-- 分数表StudentId 对应是 学生表StudentId 的外键 （外键约束）
alter table Score add constraint FK_Score_StudentId foreign key (StudentId) references Student(StudentId);
-- 分数表CourseId 对应是 课程表CourseId 的外键 （外键约束）
alter table Score add constraint FK_Score_CourseId foreign key (CourseId) references Course(CourseId);


insert into class(classname) values('软件一班')
insert into class(classname) values('软件二班')
insert into class(classname) values('计算机应用技术班')
